package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.TableManagerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import static com.swordget.commons.ColumnType.NUMBER;
import static com.swordget.commons.ColumnType.VARCHAR2;
//import static com.swordget.commons.SimpleFinalUtil.TMP_SUFFIX;

/**
 * on 2020/4/5 15:18
 * 表结构管理 mysql 实现
 * @author David
 */
public class TableManagerMysqlDao extends TableManagerOracleDao implements TableManagerDao {

    @Autowired
    private JdbcUtil jdbcUtil;

    @Autowired
    private DruidDataSource dataSource;

    /**
     * TableManagerMysqlDao log
     */
    private final static Logger LOG = LoggerFactory.getLogger(TableManagerMysqlDao.class);


    /**
     * @param tableName 表名
     * @param userName 用户名
     * @return
     */
    @Override
    public boolean dataTableExists(String tableName, String userName) {
        String userExistsSQL = String.format("SELECT COUNT(*) FROM information_schema.TABLES WHERE table_name ='%s'", tableName);
        int result = jdbcUtil.executeCount(userExistsSQL);
        return result > 0;
    }

    /**
     * @param username 用户名
     * @return
     */
    @Override
    public String getTableSpace(String username) {
        return null;
    }

    /**
     * @param tableDesign 表结构
     * @param columnDesigns 字段结构
     * @param serial 主键结构
     * @param tableSpace 表空间
     * @param username 用户名
     * @param loginId 操作用户
     * @return
     */
    @Override
    public boolean createTable(TableDesign tableDesign, List<ColumnDesign> columnDesigns, ColumnDesign serial, String tableSpace, String username, String loginId) {
        StringBuilder tableBuilder = new StringBuilder();
        tableBuilder.append(String.format("CREATE TABLE %s(", tableDesign.getTableEN()));
        for (int i = 0; i < columnDesigns.size(); i++) {
            ColumnDesign columnDesign = columnDesigns.get(i);
            String type = columnDesign.getType();
            String isNull = columnDesign.getNotNull();
            if (VARCHAR2.equals(type)) {
                tableBuilder.append(String.format("%s VARCHAR(%d) ", columnDesign.getName(),
                        columnDesign.getWidth()));
            } else if (NUMBER.equals(type)) {
                tableBuilder.append(String.format("%s DECIMAL(%d, %d) ", columnDesign.getName(),
                        columnDesign.getWidth(), columnDesign.getPoint()));
            }
            if ("Y".equals(isNull)) {
                tableBuilder.append("NOT NULL ");
            }
            tableBuilder.append(String.format("COMMENT '%s',", columnDesign.getComment()));
        }
        //添加唯一索引
        String indexNum = String.format("SYS_C%08d", Math.round(Math.random() * 100000000));
        tableBuilder.append(String.format("UNIQUE KEY %s (%s) ", indexNum, serial.getName()));

        tableBuilder.append(String.format(")ENGINE = INNODB DEFAULT CHARSET = 'UTF8'"));
        System.out.println(tableBuilder.toString());
        //执行创建表语句
        return jdbcUtil.execute(tableBuilder.toString());
    }

    @Override
    public boolean dropTable(String tableName) {
        String dropSQL = String.format("DROP TABLE IF EXISTS %s", tableName);
        //删除临时表
       /* String tmpName = tableName + TMP_SUFFIX;
        String dropTmpSQL = String.format("DROP TABLE IF EXISTS %s", tmpName);*/
        return jdbcUtil.executeBatch(dropSQL);
    }

    @Override
    public List<TableDesign> findTableDesignByPage(int rows, int page, String tableEn, String tableCn) {
        String command = String.format("SELECT * FROM TABLEDESIGN  limit %d,%d", (page - 1) * rows, (page * rows));
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(command)){
            List<TableDesign> data = new ArrayList<>();
            while (resultSet.next()) {
                data.add(TableDesign.builder()
                        .tableId(resultSet.getString("SERIALNO"))
                        .tableEN(resultSet.getString("TABLENAME"))
                        .tableCN(resultSet.getString("TABLENCOM"))
                        .loginId(resultSet.getString("CREATEUSER"))
                        .build());

            }
            return data;
        } catch (SQLException e) {
            LOG.error("SQL异常", e);
        }
        return null;
    }
}
